package com.hns.can.service.enumrate;

import com.hns.can.service.entity.BaseDriverExamUploadErrEntity;
import com.hns.frame.enumrate.MsgType;
import com.hns.frame.exception.BusinessException;
import com.hns.tool.pub.PubUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @版权：HNS 版权所有 (c) 2013
 * @author:JeffXu
 * @version Revision 1.0.0
 * @email:835264047@qq.com
 * @see:
 * @创建日期：2014-8-14
 * @功能说明：解析考勤excel文件成list对象
 * @begin
 * @修改记录:
 * @修改后版本          修改人      	修改内容
 * @2014-8-14  	         JeffXu        	创建
 * @end
 */
public class ParseDrvExamExcel {
    
    /**
     * @param file
     * @return
     * @throws IOException
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：提供对外访问的方法
     */
    public static List<BaseDriverExamUploadErrEntity> parseExcelToList(File file) throws IOException {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) { // 读取 office 2003 excel
            wb = new HSSFWorkbook(new FileInputStream(file));
        } else if ("xlsx".equals(extension)) { // 读取Office 2007 excel
            wb = new XSSFWorkbook(new FileInputStream(file));
        } else {
            file.delete();
            throw new BusinessException(MsgType.ERROR.getFlag(), "不支持的文件类型");
        }
        
        sheet = wb.getSheetAt(0);
        if (!"驾驶员考勤信息".equals(sheet.getSheetName())) {
            file.delete();
            throw new BusinessException(MsgType.ERROR.getFlag(), "您导入的模版有误。");
        }
        cell = sheet.getRow(0).getCell(0);
        if (!"驾驶员考勤信息".equals(getCellValue(cell))) {
            file.delete();
            throw new BusinessException(MsgType.ERROR.getFlag(), "您导入的模版有误。");
        }
        
        List<BaseDriverExamUploadErrEntity> list = new ArrayList<BaseDriverExamUploadErrEntity>();
        for (int i = 3; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if(PubUtil.isEmpty(row)){
            	continue;
            }
            BaseDriverExamUploadErrEntity entity = new BaseDriverExamUploadErrEntity();
            for (int j = 0; j < 6; j++) {
                cell = row.getCell(j);
                switch (j) {
                    case 0:
                        entity.setOprtLine(getCellValue(cell));
                        break;
                    case 1:
                        entity.setDrvName(getCellValue(cell));
                        break;
                    case 2:
                        entity.setLicPltNo(getCellValue(cell));
                        break;
                    case 3:
                        entity.setTimecardCardno(getCellValue(cell));
                        break;
                    case 4:
                        entity.setSignInTime(getCellValue(cell));
                        break;
                    case 5:
                        entity.setSignOutTime(getCellValue(cell));
                        break;
                }
            }
            list.add(entity);
        }
        return list;
    }
    
    /**
     * @param cell
     * @return
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：得到每个表格的值
     */
    private static String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("0");// 格式化数字
        if(cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else {
                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
            }
        }else{
            value = "";
        }
        return value;
    }
    
    public static void main(String[] args) throws IOException {
        File file = new File("x:\\test.xlsx");
        List<BaseDriverExamUploadErrEntity> list = parseExcelToList(file);
        System.out.println(list.size());
    }
    
}
